# 参考链接: 
# https://blog.csdn.net/weixin_44288604/article/details/120731317

# https://blog.csdn.net/guoxuying/article/details/112981142?utm_medium=distribute.pc_relevant.none-task-blog-2~default~baidujs_baidulandingword~default-12.pc_relevant_antiscanv2&spm=1001.2101.3001.4242.7&utm_relevant_index=15

# 注: Excel2003及其之前的版本是xls格式数据, 从Excel2007开始支持.xlsx格式
# xlsx的核心结构是XML类型结构，采用了XML的压缩方式，使其占用的空间更小，
# xlsx中最后一个x的意义就在于此，它是Excel2007版本的文件。
# xlwt模块只能写xls文件无法写入xlsx文件(文件打开有问题)
# openpyxl只能处理xlsx文件，不能处理xls文件
import openpyxl

"读取文件信息"
# xlsx_path = "/root/family/python/python_xlsx/data_metal.xlsx"
xlsx_path = "/root/family/python/python_xlsx/test.xlsx"
wb = openpyxl.load_workbook(xlsx_path)
# # 获取当前工作薄里所有的工作表
# print(wb.sheetnames)

# # 正在使用的表
# print(wb.active)

# 选择要操作的工作表， 返回工作表对象
sheet = wb['Sheet1']
# # 获取工作表的名称
# print(sheet.title)
# 修改工作表的名称
# sheet.title = 'sheet1'

# # ['Sheet1', 'Sheet2']
# # <Worksheet "Sheet2">
# # Sheet1

# 删除空表头Sheet1
# 执行删除操作：
sheet_name1 = 'Sheet1'
# 载入工作簿
workbook = openpyxl.load_workbook('SOM_Result.xlsx')
#
# 删除目标Sheet
worksheet = workbook[sheet_name1]
workbook.remove(worksheet)

# 保存已做删除处理的工作簿
workbook.save('SOM_Result.xlsx')


# 返回指定行指定列的单元格信息
# 这里计数从1开始
# cell = sheet['B1']
# print(cell)
# print(cell.row, cell.column, cell.value)
# print(sheet.cell(row=1, column=2).value)
# print(cell.coordinate)
# <Cell 'Sheet1'.B1>
# 1 B 年龄
# 年龄
# B1

# 获取指定行的数据
# cell = sheet['3']
# for i in cell:
#     print(i.value)

# 李四
# 14
# 女



# # 指定区间的数据
# cell = sheet['A1:A4']
# # 打印A1到A4的数据
# for i in cell:
#     for j in i:
#         print(j.value)

# # 姓名
# # 张三
# # 李四
# # 王五


# cell = sheet['A1:C4']
# 打印A1到C4的数据
# 这里默认自动换行了
# for i in cell:
#     for j in i:
#         print(j.value, end=",")
#     print()

# # 姓名,年龄,性别,
# # 张三,12,男,
# # 李四,14,女,
# # 王五,17,男,


# 按行获取值
# cell = sheet.iter_rows(min_row=1, max_row=4, min_col=1, max_col=3)
# # 按列获取值
# cell = sheet.iter_cols(min_row=1, max_row=4, min_col=1, max_col=3)
# for i in cell:
#     for j in i:
#         print(j.value, end=",")
#     print()


# 获取工作表中行和列的最大值
# print(sheet.max_column)
# print(sheet.max_row)
# # 3
# # 4


# 访问单元格的所有信息
# print(sheet.rows)  # 返回一个生成器， 包含文件的每一行内容， 可以通过便利访问.
# # 循环遍历每一行
# for row in sheet.rows:
#     # 循环遍历每一个单元格
#     for cell in row:
#         # 获取单元格的内容
#         print(cell.value, end=',')
#     # 换行
#     print()

# # <generator object Worksheet._cells_by_row at 0x7f6b8793b9a8>
# # 姓名,年龄,性别,
# # 张三,12,男,
# # 李四,14,女,
# # 王五,17,男,

"添加和修改文件信息"

# data = [
#         ['素子',23],
#         ['巴特',24],
#         ['塔奇克马',2]
# ]
# 按行插入
# for row in data:
#     sheet.append(row)   # 使用append插入数据
# wb.save(xlsx_path)

# # 按行获取值
# cell = sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column)
# for i in cell:
#     for j in i:
#         print(j.value, end=",")
#     print()

# # 姓名,年龄,性别,
# # 张三,12,男,
# # 李四,14,女,
# # 王五,17,男,
# # 素子,23,None,
# # 巴特,24,None,
# # 塔奇克马,2,None,


# 修改内容
# sheet['A2'] = "范里明"
# sheet.cell(2, 2).value = 24
# wb.save(xlsx_path)

# cell = sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column)
# for i in cell:
#     for j in i:
#         print(j.value, end=",")
#     print()

# 验证
# 姓名,年龄,性别,
# 范里明,24,男,
# 李四,14,女,
# 王五,17,男,
# 素子,23,None,
# 巴特,24,None,
# 塔奇克马,2,None,

# 修改某列

# cell = sheet.iter_cols(min_row=2, max_row=sheet.max_row, min_col=2, max_col=2)
# for row in cell:
#     for i, col in enumerate(row):
#         # print(i)
#         # 修改第二列的值
#         sheet.cell(i+2, 2).value = 18
#         # print(sheet.cell(i+2, 2).value)
# wb.save(xlsx_path)

# for row in cell:
#     for col in row:
#         print(col.value)




# 删除行、列

# sheet.delete_rows(idx=6)           # 删除第6行
# sheet.delete_cols(idx=2, amount=2)  # 删除第2列，及往右共2列
# wb.save(xlsx_path)


"合并、拆分单元格"
# 合并单元格有下面两种方法，需要注意的是，如果要合并的格子中有数据，即便python没有报错，Excel打开的时候也会报错。 
# merge_cells(待合并的格子编号)
# merge_cells(start_row=起始行号，start_column=起始列号，end_row=结束行号，end_column=结束列号)

# sheet.merge_cells('D1:E4')
# sheet.merge_cells(start_row=1, end_row=4, start_column=6, end_column=7)
# sheet['D1'] = "合并一"
# sheet['F1'] = "合并二"
# # 居中对齐
# cell = sheet['D1']
# alignment = openpyxl.styles.Alignment(horizontal="center", vertical="center", text_rotation=0, wrap_text=True)
# cell.alignment = alignment
# wb.save(xlsx_path)


# 拆分单个的合并单元格
# unmerge_cells(待拆分的格子编号)
# unmerge_cells(start_row=起始行号，start_column=起始列号，end_row=结束行号，end_column=结束列号)

# 该方法行不通
# sheet.unmerge_cells('D1:E4')
# 此方法可行
# sheet.merge_cells(start_row=1, end_row=7, start_column=4, end_column=5)

# wb.save(xlsx_path)



# 拆分所有的合并单元格
# #合并单元格的位置信息
# merge_coord_lists = sheet.merged_cells  
# # print(merge_coord_lists)
# # # D1:E4 F1:G4
# merge_range_list = []
# for merge_area in merge_coord_lists:
#     r1, r2, c1, c2 = merge_area.min_row, merge_area.max_row, merge_area.min_col, merge_area.max_col
#     if r2 - r1 > 0:
#         merge_range_list.append((r1, r2, c1, c2))
# print(merge_range_list)
# for r in merge_range_list:
#     sheet.unmerge_cells(start_row=r[0], end_row=r[1], start_column=r[2], end_column=r[3])   

# wb.save(xlsx_path)







# 后面还么看，看前面的就行

"自定义函数"
def readwb(wbname, sheetname=None):
    # 打开工作薄
    wb = openpyxl.load_workbook(wbname)
    # 获取要操作的工作表
    if not sheetname:
        sheet = wb.active
    else:
        sheet = wb[sheetname]
    # 获取商品信息保存到列表中
    #[ ['name', price, count]
    all_info = []
    for row in sheet.rows:
        child = [cell.value for cell in row]
        all_info.append(child)
    return sorted(all_info, key=lambda item: item[1])

def save_to_excel(data, wbname, sheetname='sheet1'):
    """
    将信息保存到excel表中;
    [[' BOOK', 50, 3], ['APPLE', 100, 1], ['BANANA', 200, 0.5]]
    """
    print("写入Excel[%s]中......." %(wbname))
    # 打开excel表, 如果文件不存在， 自己实例化一个WorkBook对象
    wb = openpyxl.Workbook()
    # 修改当前工作表的名称
    sheet = wb.active
    # 修改工作表的名称
    sheet.title = sheetname
    for row, item in enumerate(data):  # 0 [' BOOK', 50, 3]
        for column, cellValue in enumerate(item): #  0 ' BOOK'
            sheet.cell(row=row+1, column=column+1, value=cellValue)
    # ** 往单元格写入内容
    # sheet.cell['B1'].value = "value"
    # sheet.cell(row=1, column=2, value="value")
    # 保存写入的信息
    wb.save(filename=wbname)
    print("写入成功!")

# data = readwb(wbname='Book1.xlsx')
# save_to_excel(data, wbname='Book2.xlsx', sheetname="排序商品信息")





import openpyxl as xl
from openpyxl.worksheet.worksheet import Worksheet
from openpyxl.cell import MergedCell


def parser_merged_cell(sheet: Worksheet, row, col):
    """
    检查是否为合并单元格并获取对应行列单元格的值。
    如果是合并单元格，则取合并区域左上角单元格的值作为当前单元格的值,否则直接返回该单元格的值
    :param sheet: 当前工作表对象
    :param row: 需要获取的单元格所在行
    :param col: 需要获取的单元格所在列
    :return: 
    """
    cell = sheet.cell(row=row, column=col)
    if isinstance(cell, MergedCell):  # 判断该单元格是否为合并单元格
        for merged_range in sheet.merged_cell_ranges:  # 循环查找该单元格所属的合并区域
            if cell.coordinate in merged_range:
                # 获取合并区域左上角的单元格作为该单元格的值返回
                cell = sheet.cell(row=merged_range.min_row, column=merged_range.min_col)
                break
    return cell


# if __name__ == "__main__":
#     wb = xl.load_workbook("test.xlsx")
#     sheet_ = wb["Sheet1"]

#     for row_index in range(1, 4):
#         for col_index in range(1, 5):
#             cell_ = parser_merged_cell(sheet_, row_index, col_index)
#             print("第%s行第%s列：%s" % (row_index, col_index, cell_.value))





import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils import get_column_letter
from openpyxl.styles import Font, Alignment
# 自动设置excel列宽
def auto_width(filename='合并.xlsx'):
    # 打开xlsx文件
    wb = load_workbook(filename)
    # 遍历sheet
    for sheet in wb.sheetnames:
        ws = wb[sheet]
        df = pd.read_excel(filename,sheet_name=sheet, engine='openpyxl')
        # 把表头改到最后一行
        df.loc[len(df)]=list(df.columns)
        for i in df.index:
            # 设置单元格对齐方式 Alignment(horizontal=水平对齐模式,vertical=垂直对齐模式,text_rotation=旋转角度,wrap_text=是否自动换行)
            alignment = Alignment(horizontal='center', vertical='center', text_rotation=0, wrap_text=True)
            ws['A'+str(i+1)].alignment = alignment
            ws['B'+str(i+1)].alignment = alignment
            ws.row_dimensions[i].height = 20
        for col in df.columns:
            # 获取列序号
            index = list(df.columns).index(col)
            # 获取行字母表头
            letter = get_column_letter(index+1)
            # 获取当前列最大宽度
            collen = df[col].apply(lambda x :len(str(x).encode())).max()
            # 设置列宽为最大长度比例
            ws.column_dimensions[letter].width = collen*0.9
    wb.save(filename)











